package cn.edu.sdjzu.xg.bysj.dao;

import cn.edu.sdjzu.xg.bysj.domain.StudentClass;
import cn.edu.sdjzu.xg.bysj.service.DepartmentService;

import util.Condition;
import util.JdbcHelper;
import util.Pagination;

import java.sql.*;
import java.util.ArrayList;
import java.util.Collection;
import java.util.List;

public final class
StudentClassDao {
    private static StudentClassDao studentClassDao;

    private StudentClassDao() {
    }

    public static StudentClassDao getInstance() {
        // 静态变量StudentClassDao为空时，才创建StudentClassDao对象。
        // 第二次调用本方法StudentClassDao就不空了，不再创建对象。
        if (studentClassDao == null) {
            studentClassDao = new StudentClassDao();
        }
        return studentClassDao;
    }

    /**
     * @param conn   数据库连接对象
     * @param conditions_json 查询条件
     * @param pagination    分页对象
     * @return desiredStudents
     * @throws SQLException
     */
    public Collection<StudentClass> findAll( Pagination pagination,List<Condition> conditions_json,Connection conn) throws SQLException {
        Collection<StudentClass> studentClasses = new ArrayList<StudentClass>();

        int totalNum = CommonDao.getInstance().count(conn,"StudentClass");

        StringBuilder studentClass_sql = new StringBuilder("select * from StudentClass");
        //如果有条件，则生成条件子句，并附加到主语句后面
        if (conditions_json != null) {
            String whereClause = Condition.toWhereClause(conditions_json);
            studentClass_sql.append(whereClause);
        }
        //如果参数不为空，为SQL语句增加LIMIT子句
        if (pagination != null){
            studentClass_sql.append(pagination.toLimitClause(totalNum));
        }

        //在连接上创建预编译语句对象
        PreparedStatement pstmt = conn.prepareStatement(studentClass_sql.toString());
        //获得Department对象的结果集
        ResultSet resultSet = pstmt.executeQuery();

        while (resultSet.next()) {
            //根据id查找结果创建Department对象
            StudentClass studentClass = new StudentClass(resultSet.getInt("id"), resultSet.getString("description")
                    , resultSet.getString("no"), resultSet.getString("remarks"),
                    DepartmentService.getInstance().find(resultSet.getInt("department_id")));
            studentClasses.add(studentClass);
        }
        //关闭对象
        JdbcHelper.close(resultSet, pstmt);
        System.out.println(studentClass_sql);
        return studentClasses;
    }

    /**
     * @param conn   数据库连接对象
     * @param id 查询条件
     * @return desiredStudent
     * @throws SQLException
     */
    public StudentClass find(Integer id,Connection conn) throws SQLException {
        String studentClasses_sql = "select * from StudentClass where id = ?";
        //在连接上创建预编译语句对象
        PreparedStatement pstmt = conn.prepareStatement(studentClasses_sql);
        //为预编译参数赋值
        pstmt.setInt(1, id);
        //根据id查找Department
        ResultSet resultSet = pstmt.executeQuery();
        resultSet.next();
        //根据id查找结果创建Department对象
        StudentClass studentClass = new StudentClass(resultSet.getInt("id"), resultSet.getString("description")
                , resultSet.getString("no"), resultSet.getString("remarks"),
                DepartmentService.getInstance().find(resultSet.getInt("department_id")));
        //关闭
        JdbcHelper.close(resultSet,pstmt);
        return studentClass;
    }


    /**
     * @param conn   数据库连接对象
     * @param studentClass 修改的目标对象
     * @return 影响行数（是否修改成功）
     * @throws SQLException
     */
    public boolean update(StudentClass studentClass,Connection conn) throws SQLException {
        String updateStudentClass_sql = "UPDATE studentclass SET no = ?,description = ?,remarks = ?,department_id = ? WHERE id = ?";
        //在连接上创建预编译语句对象
        PreparedStatement pstmt = conn.prepareStatement(updateStudentClass_sql);
        //为预编译参数赋值
        pstmt.setString(1, studentClass.getNo());
        pstmt.setString(2, studentClass.getDescription());
        pstmt.setString(3, studentClass.getRemarks());
        pstmt.setInt(4, studentClass.getDepartment().getId());
        pstmt.setInt(5, studentClass.getId());

        //修改Department 并判断是否修改成功
        int affectedRowNum = pstmt.executeUpdate();
        JdbcHelper.close(null,pstmt);
        return affectedRowNum >0;
    }

    /**
     * @param conn   数据库连接对象
     * @param studentClass 增加条件
     * @return 影响行数（是否增加成功）
     * @throws SQLException
     */
    public boolean add(StudentClass studentClass,Connection conn) throws SQLException {
        String addStudentClass_sql = "INSERT INTO studentclass(description,no,remarks,department_id) VALUES" + "(?,?,?,?)";
        //在连接上创建预编译语句对象
        PreparedStatement pstmt = conn.prepareStatement(addStudentClass_sql);
        //为预编译参数赋值
        pstmt.setString(1, studentClass.getDescription());
        pstmt.setString(2, studentClass.getNo());
        pstmt.setString(3, studentClass.getRemarks());
        pstmt.setInt(4, studentClass.getDepartment().getId());
        //添加Department 并判断是否添加成功
        int affectedRowNum = pstmt.executeUpdate();
        JdbcHelper.close(null,pstmt);
        return affectedRowNum >0;
    }

    /**
     * @param conn   数据库连接对象
     * @param id 删除条件
     * @return 影响行数（是否删除成功）
     * @throws SQLException
     */
    public boolean delete(Integer id,Connection conn) throws SQLException {
        String deleteStudentClass_sql = "delete from studentclass where id = ?";
        //在连接上创建预编译语句对象
        PreparedStatement pstmt = conn.prepareStatement(deleteStudentClass_sql);
        //为预编译参数赋值
        pstmt.setInt(1, id);
        //删除Department 并判断是否删除成功
        int affectedRowNum = pstmt.executeUpdate();
        JdbcHelper.close(null,pstmt);
        return affectedRowNum >0;
    }
}

